#-*- coding:utf-8 -*
import pandas as pd
import requests
import csv
import pymysql


# 1, 2a91614ddbe26a55ef49080fd41eb17c
# 2, 9fee184fb377ef9c89e1dc1240bb789c
# 3, 5d69d190e9d7e49c3dcf39085cdb021d
# 4, e5a0f66af22f849e6090e50cb2b83827
# 5, c3381c1f5a3511dccb13fbb2c4be990b
# 6, e5d32adcd92a4a899ed5e894882b287e
# 7, 0e3c42697047aaf1f4d331a2269bf723
# 8, 9d7c9d4a3155be8dd1621401d5e8488d   split6.csv
# 9, a43b18ad60332eb312f9f69fdf17c384   split7.csv
# 10, a63fc527248fb718b2a154915487ce1b  split8.csv
# 11, c9e6a9daad79d6c26e502855e803ba6e  split9.csv
# 12, 20ffc53e02aa8114b7a7f5aa4b07032a  split10.csv
# 13, 86131414505a246105d156f12362520c  split11.csv
# 14, 224d481ab920d8254d330660b2d19230  split12.csv
# 15, ed693d56738ea58007bdb1adc6b591ac   split13.csv
# 16, 26b2f80ad49693a0dd0faabbd73215d7  split14.csv
# 17, b1a655a8a0cba75cf7a609caf17135bf  split15.csv
# 18, 1e7be0c48c2620b05f0f0b1d172c57fc  split16.csv
# 19, 2d504bf7a0ad07df52d758081e02da65  split17.csv
# 20, 2f3c9b4dd6630156ad162930035e5cf2  split18.csv
def transform(geo):
    parameters = {'address': geo, 'key': '0e3c42697047aaf1f4d331a2269bf723'}
    base = 'https://restapi.amap.com/v3/geocode/geo'
    loc = 0
    try:
        response = requests.get(base, parameters, timeout=2)
        if response.status_code == 200:
            answer = response.json()
            loc = answer['geocodes'][0]
            loc1 = loc['location']
            if not loc1 is None:
                return loc1
        else:
            print("请求失败")
    except :


       print(geo, "+++++++", "没有拿到经纬度信息", "+++++", "设为空值")



if __name__ == "__main__":

    with open('E:\Studygit\python_learning\GetBankInfo\code\/filter.csv', 'r', encoding='utf-8') as csvfile:

        reader = csv.reader(csvfile)
        rows = [row for row in reader]


    #print(rows)
    connection = pymysql.connect(host='192.168.11.23', user='root', passwd='123456', port=30306,
                                 db='bank', charset="utf8")
    num  = 0
    j = 0
    for item in rows[j:]:
        num = num + 1
        #print(item[6])
        print(num, [item[0], item[1], item[2], item[3], item[4], transform(item[4])])
        branch_id = item[0]
        branch_name = item[1]
        branch_tel = item[2]
        bank_zip = item[3]
        bank_address = item[4]
        location = transform(item[4])
        #print(location)
        if location is None:
            longitude = ""
            latitude = ""
        else:
           longitude = location.split(',')[0]
           latitude = location.split(',')[1]

        sql = "replace into bankinfo(branch_id, branch_name, branch_tel, bank_zip, bank_address, longitude, latitude) values('%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (
            branch_id, branch_name, branch_tel, bank_zip, bank_address, longitude, latitude)

        cursor = connection.cursor()

        print(sql)
        cursor.execute(sql)
        connection.commit()

